package com.example.lj.uav.dao;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.example.lj.uav.bean.CameraItem;
import com.example.lj.uav.bean.PointAction;
import com.example.lj.uav.bean.RouteLine;
import com.example.lj.uav.bean.RoutePoint;
import com.example.lj.uav.utils.MyDBHelper;

import java.util.ArrayList;
import java.util.List;

public class MyDBDao {
    private Context context;
    private MyDBHelper helper;
    private SQLiteDatabase db;
    private static MyDBDao dao;

    private MyDBDao(Context context) {
        this.context = context;
        if (helper==null){
            helper = new MyDBHelper(context);
            db =  helper.getWritableDatabase();

            helper.onCreate(db);
        }
    }

    public static MyDBDao getInstance(Context context){
        if (dao==null){
            dao = new MyDBDao(context);
        }
        return dao;
    }

    public void addRoute(RouteLine line
    ){
        try {
            db =  helper.getWritableDatabase();
            db.beginTransaction();
            String sql = "insert into " + MyDBHelper.TABLE_ROUTE + " (FileName, altitude, autoFlightSpeed, actionOnFinish,headingMode,gimbalPitchMode,altitudeMode,coordinates,updateTime,updatePlace,collected,type,image_path,stopWhenLost) " +
                    "values ("+"'"+line.getFileName()+"',"
                    + line.getAltitude()+","
                    +line.getSpeed()+",'"
                    +line.getActionOnFinish()+"','"
                    +line.getHeadingMode()+"','"
                    +line.getGimbalPitchMode()+"','"
                    +line.getAltitudeMode()+"','"
                    +line.getCoordinates()+"',"
                    +line.getUpdateTime()+",'"
                    +line.getUpdatePlace()+"',"
                    +line.getCollected()+",'"
                    +line.getType()+"','"
                    +line.getImagePath()+"',"
                    +line.getStopWhenLost()+
                    ")"
                    ;
            Log.e("info","addRoute = "+sql);
            db.execSQL(sql);

            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void deleteRoute(String file_name){
        try{
            String sql = "delete from " +MyDBHelper.TABLE_ROUTE + " where FileName = '"+file_name+"'";
            Log.e("info","deleteRoute = "+sql);
            db = helper.getWritableDatabase();
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void updateRoute(RouteLine line,
                            String oldFileName
    ){
//        db = helper.getWritableDatabase();
//        db.beginTransaction();
//        String sql = "update "+MyDBHelper.TABLE_ROUTE+
//                "set FileName = "+fileName+
//                ",altitude = " +
//                altitude +
//                ",autoFlightSpeed="+speed+
//                ",actionOnFinish="+actionOnFinish+
//                ",headingMode="+headingMode+
//                ",gimbalPitchMode="+gimbalPitchMode+
//                ",altitudeMode ="+altitudeMode+
//                ",coordinates ="+coordinates+
//                ",updateTime ="+updateTime+
//                ",updatePlace ="+updatePlace+
//                " where id = "+id;
//        db.execSQL(sql);
//        db.setTransactionSuccessful();
        deleteRoute(oldFileName);
        addRoute(line);
    }

    public List<RouteLine> queryRoute(){
        List<RouteLine> list = new ArrayList<>();
        try{
            db =  helper.getWritableDatabase();
            db.beginTransaction();
            Cursor cursor = db.query(MyDBHelper.TABLE_ROUTE,null,null,null,null,null,null);
            while (cursor.moveToNext()){
                RouteLine line = new RouteLine();
                line.setId(cursor.getInt(0));
                line.setFileName(cursor.getString(1));
                line.setAltitude(cursor.getDouble(2));
                line.setSpeed(cursor.getFloat(3));
                line.setActionOnFinish(cursor.getString(4));
                line.setHeadingMode(cursor.getString(5));
                line.setGimbalPitchMode(cursor.getString(6));
                line.setAltitudeMode(cursor.getString(7));
                line.setCoordinates(cursor.getString(8));
                line.setUpdateTime(cursor.getLong(9));
                line.setUpdatePlace(cursor.getString(10));
                line.setCollected(cursor.getInt(11));
                line.setType(cursor.getString(12));
                line.setImagePath(cursor.getString(13));
                line.setStopWhenLost(cursor.getInt(14));
                list.add(line);
            }
            cursor.close();
            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return list;
    }

    public List<RouteLine> queryCollectedRoute(){
        List<RouteLine> list = new ArrayList<>();
        try {
            db =  helper.getWritableDatabase();
            db.beginTransaction();
            String condition = "collected = ?";
            String[] args = {"1"};
            Cursor cursor = db.query(MyDBHelper.TABLE_ROUTE,null,condition,args,null,null,"updateTime");
            while (cursor.moveToNext()){
                RouteLine line = new RouteLine();
                line.setId(cursor.getInt(0));
                line.setFileName(cursor.getString(1));
                line.setAltitude(cursor.getDouble(2));
                line.setSpeed(cursor.getFloat(3));
                line.setActionOnFinish(cursor.getString(4));
                line.setHeadingMode(cursor.getString(5));
                line.setGimbalPitchMode(cursor.getString(6));
                line.setAltitudeMode(cursor.getString(7));
                line.setCoordinates(cursor.getString(8));
                line.setUpdateTime(cursor.getLong(9));
                line.setUpdatePlace(cursor.getString(10));
                line.setCollected(cursor.getInt(11));
                line.setType(cursor.getString(12));
                list.add(line);
            }
            cursor.close();
            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    public void addPoint(int id,RoutePoint point,String file_name){
        try {
            db =  helper.getWritableDatabase();
            db.beginTransaction();
            String sql = "insert into " + MyDBHelper.TABLE_POINT + " (id, latitude, longitude,altitude,altitudeMode,direction,angle,pitchAngle,motion, file_name) " +
                    "values ("+
                    id+","+
                    point.getLatitude()+","+
                    point.getLongitude()+","+
                    point.getAltitude()+","+
                    "'"+point.getUseWaylineAltitude()+"',"+
                    "'"+point.getDirection()+"',"+
                    point.getAngle()+","+
                    point.getPitchAngle()+","+
                    "'"+point.getMotion()+"',"+
                    "'"+file_name+"')";
            Log.e("info","addPoints = "+sql);
            db.execSQL(sql);

            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }

    }

    public void deletePoints(String file_name){
        try {
            String sql = "delete from " +MyDBHelper.TABLE_POINT + " where file_name = '"+file_name+"'";
            Log.e("info","deletePoints = "+sql);
            db = helper.getWritableDatabase();
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void updatePoints(String file_name,List<RoutePoint> points){
        deletePoints(file_name);
        for (int i=0;i<points.size();i++){
            addPoint(i,points.get(i),file_name);
        }
    }

    public List<RoutePoint> queryPoints(String file_name){
        try {
            List<RoutePoint> list = new ArrayList<>();
            String[] columns = {"id","latitude","longitude","altitude","altitudeMode","direction","angle","pitchAngle","motion","file_name"};
            String condition = "file_name = ?";
            String[] args = {file_name};
            db =  helper.getWritableDatabase();
            db.beginTransaction();
            Cursor cursor = db.query(MyDBHelper.TABLE_POINT,columns,condition,args,null,null,"id");
//        if (cursor.moveToFirst()){
//            for (int i=0;i<cursor.getCount();i++){
//
//                cursor.move(i);
//
//            }

//        }
            while (cursor.moveToNext()){
                RoutePoint point = new RoutePoint();
                int id = cursor.getInt(0);
                point.setId(id);
                point.setLatitude(cursor.getDouble(1));
                point.setLongitude(cursor.getDouble(2));
                point.setAltitude(cursor.getDouble(3));
                point.setUseWaylineAltitude(cursor.getString(4));
                point.setDirection(cursor.getString(5));
                point.setAngle(cursor.getDouble(6));
                point.setPitchAngle(cursor.getDouble(7));
                point.setMotion(cursor.getString(8));
                point.setActions(queryActions(file_name,id));
                list.add(point);
            }
            cursor.close();
            db.setTransactionSuccessful();
            db.endTransaction();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    public void addPointActions(String file_name,RoutePoint point){
        try {
            db =  helper.getWritableDatabase();
            db.beginTransaction();
            List<PointAction> actions = point.getActions();
            for(int i=0;i<actions.size();i++){
                String sql = "insert into " + MyDBHelper.TABLE_POINT_ACTION + " (Id,point_id,point_action,param,file_name ) " +
                        "values ("+i+"," +
                        point.getId()+",'"
                        + actions.get(i).getName()+"','"
                        + actions.get(i).getParam()+"','"
                        +file_name+"')";
                Log.e("info","addRoute = "+sql);
                db.execSQL(sql);
            }
            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void deletePointActions(String file_name,int point_id){
        try {
            String sql = "delete from " +MyDBHelper.TABLE_POINT_ACTION + " where file_name = '"+file_name+"' and point_id = "+point_id;
            db = helper.getWritableDatabase();
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public List<PointAction> queryActions(String file_name,int id){
        List<PointAction> actions = new ArrayList<>();
//        db =  helper.getWritableDatabase();
//        db.beginTransaction();
        String condition = "file_name = ? and point_id = ?";
        String[] args = {file_name,id+""};
        Cursor cursor = db.query(MyDBHelper.TABLE_POINT_ACTION,null,condition,args,null,null,"Id");
        while (cursor.moveToNext()){
            PointAction action = new PointAction();
            action.setId(cursor.getInt(0));
            action.setName(cursor.getString(2));
            action.setParam(cursor.getString(3));
            actions.add(action);
        }
        cursor.close();
//        db.setTransactionSuccessful();
//        db.endTransaction();
        return actions;
    }

    public void updatePointActions(String file_name,List<RoutePoint> points){
        deletePointActions(file_name);
        for (RoutePoint point:points){
            addPointActions(file_name,point);
        }
    }

    private void deletePointActions(String file_name) {
        try {
            String sql = "delete from " +MyDBHelper.TABLE_POINT_ACTION + " where file_name = '"+file_name+"'";
            db = helper.getWritableDatabase();
            db.beginTransaction();
            db.execSQL(sql);
            db.setTransactionSuccessful();
            db.endTransaction();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public void addCamera(CameraItem item){
        db =  helper.getWritableDatabase();
        db.beginTransaction();
        String sql = "insert into " + MyDBHelper.TABLE_CAMERA + " (name,sensorW,sensorH,focal_length ) " +
                "values ('"+item.getName()+"','" +
                item.getSensorW()+"','"
                + item.getSensorH()+"','"
                + item.getFocal_length()+"')";
        db.execSQL(sql);
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    public void deleteCamera(String name){
        db =  helper.getWritableDatabase();
        db.beginTransaction();
        String sql = "delete from " +MyDBHelper.TABLE_POINT_ACTION + " where name = '"+name+"'";
        db.execSQL(sql);
        db.setTransactionSuccessful();
        db.endTransaction();
    }

    public void upDateCamera(String oldName,CameraItem item){
        deleteCamera(oldName);
        addCamera(item);
    }

    public List<CameraItem> queryCamera(){
        List<CameraItem> list = new ArrayList<>();
        Cursor cursor = db.query(MyDBHelper.TABLE_CAMERA,null,null,null,null,null,null);
        while (cursor.moveToNext()){
            CameraItem cameraItem = new CameraItem();
            cameraItem.setName(cursor.getString(0));
            cameraItem.setSensorW(cursor.getString(1));
            cameraItem.setSensorH(cursor.getString(2));
            cameraItem.setFocal_length(cursor.getString(3));
            list.add(cameraItem);
        }
        cursor.close();
        return list;
    }
}
